/*
	EXEC SP_CREATE_UPDATE_TRANSFER_MATERAIL_LOT 13358, 435, '', '', 'MANULLAY INSERT', 'Assembly', 'PROD', 'ENGG','Admin'
*/
ALTER PROC SP_CREATE_UPDATE_TRANSFER_MATERAIL_LOT
(
	@LOT_ID				INT,
	@TRANSFER_QTY_IN	INT,
	@TRANSFER_WAFERIDS	VARCHAR(100),
	@REMAIN_WAFERIDS	VARCHAR(100),
	@COMMENTS 			VARCHAR(500),
	@PS_TYPE_NAME		VARCHAR(50),
	@TRANSFER_FROM		VARCHAR(50),
	@TRANSFER_TO		VARCHAR(50),
	@USER_NAME			VARCHAR(50)
)
AS
BEGIN
	
	DECLARE @PRV_TRANSFERED_WAFERIDS 		VARCHAR(300)
	DECLARE @PRV_TRANSFERED_WAFERIDS_AS_COMMA	 VARCHAR(300)
    DECLARE @TRANSFERED_WAFERIDS_AS_COMMA	 VARCHAR(300)
	DECLARE @PRV_TRANSFERED_WAFER_IDS_TABLE 	TABLE ( WAFER_ID INT )
	DECLARE	@TRANSFERED_WAFER_IDS_TABLE    TABLE ( WAFER_ID INT )
	DECLARE @WAFER_IDS_TABLE 		TABLE ( WAFER_ID INT )
	DECLARE @CONCAT_WAFERS_INTO_STRING 	VARCHAR(300)
	
	DECLARE @LOCATION_ID INT
	DECLARE @PROCESSSTEP_ID INT
	DECLARE @LOT_NUMBER VARCHAR(50)
	DECLARE @NEW_LOT_NUMBER VARCHAR(50)
	DECLARE @FAB_LOT_NUMBER VARCHAR(50)
	DECLARE @LOT_CATEGORY VARCHAR(50)
	DECLARE @LOT_STATUS	  VARCHAR(50) 
	DECLARE @NEW_LOT_ID  INT
	DECLARE @FROMLOT_ID  INT
	DECLARE @TRANSFER_TO_CATEGORY VARCHAR(10)
	DECLARE @TRANSFER_FROM_CATEGORY VARCHAR(10)
 
	SELECT @PROCESSSTEP_ID = PROCESSSTEP_ID , @LOCATION_ID = LOCATION_ID, @LOT_NUMBER = LOT_NUMBER, @FAB_LOT_NUMBER = FAB_LOT_NUMBER  FROM LOT_TABLE WHERE LOT_ID = @LOT_ID

	SELECT @TRANSFER_TO_CATEGORY = CATEGORY FROM Department_Table WHERE DEPARTMENT_NAME = @TRANSFER_TO
	SELECT @TRANSFER_FROM_CATEGORY = CATEGORY FROM Department_Table WHERE DEPARTMENT_NAME = @TRANSFER_FROM
	
	IF @TRANSFER_TO_CATEGORY <> 'PROD'
	BEGIN
		SET @LOT_CATEGORY = 'ENGG'

		IF @TRANSFER_FROM_CATEGORY = 'PROD'
		BEGIN
			SET @NEW_LOT_NUMBER = @LOT_NUMBER + '-E'
		END
		ELSE
		BEGIN
			SET @NEW_LOT_NUMBER = @LOT_NUMBER
		END
	END
	ELSE
	BEGIN
		SET @LOT_CATEGORY = 'PROD'
		SELECT @FROMLOT_ID = FROM_LOT_ID FROM Transferred_Lots WHERE LOT_ID = @LOT_ID
		IF @FROMLOT_ID IS NOT NULL
		BEGIN
			SELECT @NEW_LOT_NUMBER = LOT_NUMBER FROM LOT_TABLE WHERE LOT_ID = @FROMLOT_ID
		END
		ELSE
		BEGIN
			SET @NEW_LOT_NUMBER = @LOT_NUMBER
		END
	END
	
	SELECT @NEW_LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @NEW_LOT_NUMBER
  
 	IF @NEW_LOT_ID IS NULL -- Check if the lot exists
 	BEGIN 
		IF @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT' OR @PS_TYPE_NAME = 'BUMPING'
		BEGIN	
			-- Create Lot
 			INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, Wafer_Qty_In, DATE_IN, LOT_CURRENT_STATUS, TIMESTAMP, LOT_ENTERED_BY, LOT_CATEGORY, LOT_DROP_OUT, [USER_NAME]) 
				VALUES (@PROCESSSTEP_ID, @LOCATION_ID, @NEW_LOT_NUMBER, @FAB_LOT_NUMBER, @NEW_LOT_NUMBER, @TRANSFER_QTY_IN, CONVERT( VARCHAR, GETDATE(), 101 ), 'Running', CURRENT_TIMESTAMP,0, @Lot_Category, 0, @USER_NAME)
			SELECT @NEW_LOT_ID = @@IDENTITY
		END
		ELSE
		BEGIN
			-- Create Lot
 			INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, Qty_In, DATE_IN, LOT_CURRENT_STATUS, TIMESTAMP, LOT_ENTERED_BY, LOT_CATEGORY, LOT_DROP_OUT, [USER_NAME]) 
				VALUES (@PROCESSSTEP_ID, @LOCATION_ID, @NEW_LOT_NUMBER, @FAB_LOT_NUMBER, @NEW_LOT_NUMBER, @TRANSFER_QTY_IN, CONVERT( VARCHAR, GETDATE(), 101 ), 'Running', CURRENT_TIMESTAMP,0, @Lot_Category, 0, @USER_NAME)
			SELECT @NEW_LOT_ID = @@IDENTITY
		END	
		
		IF @PS_TYPE_NAME = 'FAB'
		BEGIN
			INSERT INTO FAB_LOT (LOT_ID, Wafer_Id, Timestamp) VALUES ( @NEW_LOT_ID, @TRANSFER_WAFERIDS, CURRENT_TIMESTAMP )  
		END
		ELSE IF @PS_TYPE_NAME = 'SORT'
		BEGIN
			INSERT INTO SORT_LOT (LOT_ID, Sl_Wafer_ID, Timestamp) VALUES ( @NEW_LOT_ID, @TRANSFER_WAFERIDS, CURRENT_TIMESTAMP )  
		END
		ELSE IF @PS_TYPE_NAME = 'BUMPING'
		BEGIN
			INSERT INTO BUMP_LOT (LOT_ID, Wafer_ID, Timestamp) VALUES ( @NEW_LOT_ID, @TRANSFER_WAFERIDS, CURRENT_TIMESTAMP )  
		END
		ELSE IF @PS_TYPE_NAME = 'Wafer Store' OR @PS_TYPE_NAME = 'Die Bank' OR @PS_TYPE_NAME = 'Class Store' OR @PS_TYPE_NAME = 'Finished Goods'
		BEGIN
			IF @PS_TYPE_NAME = 'Finished Goods'
			BEGIN
				insert into Inventory_Lot( Lot_ID, IL_Qty_IN, IL_Qty_Balance, Timestamp ) values (@NEW_LOT_ID, @TRANSFER_QTY_IN, @TRANSFER_QTY_IN, CURRENT_TIMESTAMP)		
			END
			ELSE
			BEGIN
				insert into Inventory_Lot( Lot_ID, Wafer_id, Timestamp ) values (@NEW_LOT_ID, @TRANSFER_WAFERIDS, CURRENT_TIMESTAMP)		
			END
		END
		ELSE IF @PS_TYPE_NAME = 'Assembly' 
		BEGIN
			insert into Assembly_Lot( Lot_ID, Timestamp ) values (@NEW_LOT_ID, CURRENT_TIMESTAMP)		
		END
		ELSE IF @PS_TYPE_NAME = 'TEST' 
		BEGIN
			insert into Final_Test_Lot( Lot_ID, Timestamp ) values (@NEW_LOT_ID, CURRENT_TIMESTAMP)		
		END
	END
	ELSE
	BEGIN
		SELECT @LOT_STATUS = LOT_CURRENT_STATUS FROM LOT_TABLE WHERE LOT_ID = @NEW_LOT_ID
		IF @LOT_STATUS <> 'Close' 
		BEGIN
			IF @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT' OR @PS_TYPE_NAME = 'BUMPING'
			BEGIN
				UPDATE LOT_TABLE SET WAFER_QTY_IN = WAFER_QTY_IN + @TRANSFER_QTY_IN, LOT_DROP_OUT = 0 WHERE LOT_ID = @NEW_LOT_ID
			END
			ELSE
			BEGIN
				UPDATE LOT_TABLE SET QTY_IN = QTY_IN + @TRANSFER_QTY_IN, LOT_DROP_OUT = 0 WHERE LOT_ID = @NEW_LOT_ID
			END
		END
		ELSE
		BEGIN
			IF @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT' OR @PS_TYPE_NAME = 'BUMPING' 
			BEGIN
				UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Running', WAFER_QTY_IN = @TRANSFER_QTY_IN, LOT_DROP_OUT = 0 WHERE LOT_ID = @NEW_LOT_ID
			END
			ELSE
			BEGIN
				UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Running', QTY_IN = @TRANSFER_QTY_IN, LOT_DROP_OUT = 0 WHERE LOT_ID = @NEW_LOT_ID
			END		
		END

		IF @PS_TYPE_NAME = 'FAB'
		BEGIN
			UPDATE FAB_LOT SET WAFER_ID = ISNULL(WAFER_ID,'') + ';' + @TRANSFER_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @NEW_LOT_ID
		END
		ELSE IF @PS_TYPE_NAME = 'SORT'
		BEGIN
			UPDATE SORT_LOT SET SL_WAFER_ID = ISNULL(SL_WAFER_ID,'') + ';' + @TRANSFER_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @NEW_LOT_ID
		END
		ELSE IF @PS_TYPE_NAME = 'BUMPING'
		BEGIN
			UPDATE BUMP_LOT SET WAFER_ID = ISNULL(WAFER_ID,'') + ';' + @TRANSFER_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @NEW_LOT_ID
		END
		ELSE IF @PS_TYPE_NAME = 'Wafer Store' OR @PS_TYPE_NAME = 'Die Bank' OR @PS_TYPE_NAME = 'Class Store' OR @PS_TYPE_NAME = 'Finished Goods'
		BEGIN
			UPDATE INVENTORY_LOT SET WAFER_ID = ISNULL(WAFER_ID,'') + ';' + @TRANSFER_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @NEW_LOT_ID
		END
	END		

	IF @TRANSFER_QTY_IN > 0 AND @NEW_LOT_ID > 0
	BEGIN

			IF( ( SELECT COUNT(*) FROM Transferred_Lots WHERE LOT_ID = @NEW_LOT_ID AND TRANSFERRED_DATE = CONVERT( VARCHAR, GETDATE(), 101 ) AND TRANSFERRED_FROM = @TRANSFER_FROM AND TRANSFERRED_TO = @TRANSFER_TO  ) = 0 ) 
			BEGIN
				--SELECT * FROM Transferred_Lots
				IF @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT' OR @PS_TYPE_NAME = 'BUMPING' 
				BEGIN
					INSERT INTO Transferred_Lots ( LOT_ID, FROM_LOT_ID, TRANSFERRED_WAFER_QTY, TRANSFERRED_WAFER_IDS, TRANSFERRED_DATE, TRANSFERRED_BY, TRANSFERRED_FROM,TRANSFERRED_TO, [TIMESTAMP], COMMENTS )
					VALUES ( @NEW_LOT_ID, @LOT_ID, @TRANSFER_QTY_IN, @TRANSFER_WAFERIDS, CONVERT( VARCHAR, GETDATE(), 101 ), @USER_NAME, @TRANSFER_FROM,@TRANSFER_TO, CURRENT_TIMESTAMP, @COMMENTS )
				END
				ELSE
				BEGIN
					INSERT INTO Transferred_Lots ( LOT_ID, FROM_LOT_ID, TRANSFERRED_DIE_QTY, TRANSFERRED_WAFER_IDS, TRANSFERRED_DATE, TRANSFERRED_BY, TRANSFERRED_FROM,TRANSFERRED_TO, [TIMESTAMP], COMMENTS )
					VALUES ( @NEW_LOT_ID, @LOT_ID, @TRANSFER_QTY_IN, @TRANSFER_WAFERIDS, CONVERT( VARCHAR, GETDATE(), 101 ), @USER_NAME, @TRANSFER_FROM,@TRANSFER_TO, CURRENT_TIMESTAMP, @COMMENTS )	
				END
			END
			ELSE
			BEGIN
				
				/* Partial Transfer for on same Day One wafer is useful*/
				SELECT @PRV_TRANSFERED_WAFERIDS = TRANSFERRED_WAFER_IDS FROM Transferred_Lots WHERE LOT_ID = @NEW_LOT_ID AND TRANSFERRED_DATE = CONVERT( VARCHAR, GETDATE(), 101 ) AND TRANSFERRED_FROM = @TRANSFER_FROM AND TRANSFERRED_TO = @TRANSFER_TO

				SET @PRV_TRANSFERED_WAFERIDS_AS_COMMA = REPLACE(@PRV_TRANSFERED_WAFERIDS, ';',',')
				SET @TRANSFERED_WAFERIDS_AS_COMMA = REPLACE(@TRANSFER_WAFERIDS, ';',',')

				INSERT INTO @PRV_TRANSFERED_WAFER_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @PRV_TRANSFERED_WAFERIDS_AS_COMMA   )		
				INSERT INTO @TRANSFERED_WAFER_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @TRANSFERED_WAFERIDS_AS_COMMA  )		
				
				INSERT INTO @WAFER_IDS_TABLE SELECT WAFER_ID FROM @PRV_TRANSFERED_WAFER_IDS_TABLE UNION SELECT WAFER_ID FROM @TRANSFERED_WAFER_IDS_TABLE

				SET @CONCAT_WAFERS_INTO_STRING = NULL
		
				SELECT @CONCAT_WAFERS_INTO_STRING = COALESCE( @CONCAT_WAFERS_INTO_STRING + ';' + CONVERT( VARCHAR(2), WAFER_ID ), CONVERT( VARCHAR(2) , WAFER_ID ) )
						FROM @WAFER_IDS_TABLE ORDER BY WAFER_ID 
				/* END Partial Transfer Wafers */
				IF @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT' OR @PS_TYPE_NAME = 'BUMPING' 
				BEGIN
					UPDATE Transferred_Lots SET TRANSFERRED_WAFER_QTY 	= ISNULL( TRANSFERRED_WAFER_QTY, 0 )+ @TRANSFER_QTY_IN, 
								 TRANSFERRED_DATE	= CONVERT( VARCHAR, GETDATE(), 101 ),
								 TRANSFERRED_WAFER_IDS 	= @CONCAT_WAFERS_INTO_STRING, 
								 TRANSFERRED_UPDATE_BY	= @USER_NAME,
								 TRANSFERRED_UPDATE_ON  =  CONVERT( VARCHAR, GETDATE(), 101 ),  	
								 [TIMESTAMP] 		= CURRENT_TIMESTAMP,
								 COMMENTS		= @COMMENTS
					WHERE LOT_ID = @NEW_LOT_ID AND TRANSFERRED_DATE = CONVERT( VARCHAR, GETDATE(), 101 ) AND TRANSFERRED_FROM = @TRANSFER_FROM AND TRANSFERRED_TO = @TRANSFER_TO
				END
				ELSE
				BEGIN
						UPDATE Transferred_Lots SET TRANSFERRED_DIE_QTY 	= ISNULL( TRANSFERRED_DIE_QTY, 0 )+ @TRANSFER_QTY_IN, 
								 TRANSFERRED_DATE	= CONVERT( VARCHAR, GETDATE(), 101 ),
								 TRANSFERRED_WAFER_IDS 	= @CONCAT_WAFERS_INTO_STRING, 
								 TRANSFERRED_UPDATE_BY	= @USER_NAME,
								 TRANSFERRED_UPDATE_ON  =  CONVERT( VARCHAR, GETDATE(), 101 ),  	
								 [TIMESTAMP] 		= CURRENT_TIMESTAMP,
								 COMMENTS		= @COMMENTS
					WHERE LOT_ID = @NEW_LOT_ID AND TRANSFERRED_DATE = CONVERT( VARCHAR, GETDATE(), 101 ) AND TRANSFERRED_FROM = @TRANSFER_FROM AND TRANSFERRED_TO = @TRANSFER_TO
				END
			END	-- END OF ELSE BLOCK	
			
			IF @PS_TYPE_NAME = 'FAB'
			BEGIN
				UPDATE FAB_LOT SET WAFER_ID = @REMAIN_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
			END
			ELSE IF @PS_TYPE_NAME = 'SORT'
			BEGIN
				UPDATE SORT_LOT SET SL_WAFER_ID = @REMAIN_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
			END
			ELSE IF @PS_TYPE_NAME = 'BUMPING'
			BEGIN
				UPDATE BUMP_LOT SET WAFER_ID = @REMAIN_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
			END
			ELSE IF @PS_TYPE_NAME = 'Wafer Store' OR @PS_TYPE_NAME = 'Die Bank'
			BEGIN
				UPDATE INVENTORY_LOT SET WAFER_ID = @REMAIN_WAFERIDS , TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
			END
			ELSE IF @PS_TYPE_NAME = 'Finshed Goods' 
			BEGIN
				UPDATE INVENTORY_LOT SET IL_Qty_IN = IL_Qty_IN-@TRANSFER_QTY_IN, IL_Qty_Balance = IL_Qty_Balance-@TRANSFER_QTY_IN, TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
			END
			IF @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT' OR @PS_TYPE_NAME = 'BUMPING' 
			BEGIN
					UPDATE LOT_TABLE SET WAFER_QTY_IN = WAFER_QTY_IN - @TRANSFER_QTY_IN WHERE LOT_ID = @LOT_ID
					/* CLOSING LOT */
					IF( ( SELECT WAFER_QTY_IN FROM LOT_TABLE WHERE LOT_ID = @LOT_ID ) <= 0 )
					BEGIN
						UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Close' WHERE LOT_ID = @LOT_ID
					END
			END
			ELSE
			BEGIN
					UPDATE LOT_TABLE SET QTY_IN = QTY_IN - @TRANSFER_QTY_IN WHERE LOT_ID = @LOT_ID
					/* CLOSING LOT */
					IF( ( SELECT QTY_IN FROM LOT_TABLE WHERE LOT_ID = @LOT_ID ) <= 0 )
					BEGIN
						UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Close' WHERE LOT_ID = @LOT_ID
					END
			END
	END
END